package cn.edu.sdjzu.xg.bysj.dao;
//201902104061徐中堂
import cn.edu.sdjzu.xg.bysj.domain.Department;
import cn.edu.sdjzu.xg.bysj.domain.Teacher;
import com.alibaba.fastjson.JSON;
import lombok.extern.slf4j.Slf4j;
import util.CommonDao;
import util.Condition;
import util.JdbcHelper;
import util.Pagination;

import java.sql.*;
import java.util.Collection;
import java.util.TreeSet;

@Slf4j public final class TeacherDao {
    //新建TeacherDao的实例对象
    private static TeacherDao teacherDao = new TeacherDao();
    //预编译盒子
    private PreparedStatement preparedStatement;
    //结果集
    private ResultSet resultSet;
    private TeacherDao() {
    }

    //获得实例
    public static TeacherDao getInstance() {
        return teacherDao;
    }

    /**
     *
     * @param condition
     * @param pagination
     * @return
     * @throws SQLException
     */
    public Collection<Teacher> findAll(Connection connection,Collection<Condition> condition, Pagination pagination) throws SQLException{
        //teachers集合用于储存查找到的对象
        Collection<Teacher> teachers = new TreeSet<Teacher>();
        //构建查找sql语句
        StringBuilder sql_select= new StringBuilder("SELECT * FROM teacher");
        if (condition != null){
            //如果condition_json带有请求，则根据请求构建对应的WHERE子句
            String whereClause = Condition.toWhereClause(JSON.toJSONString(condition));
            //将构建好的where子句添加到查找sql语句后
            sql_select.append(whereClause);
            log.trace(sql_select.toString());
        }
        if (pagination != null){
            int totalNum = CommonDao.getInstance().count(connection,"Teacher");
            sql_select.append(pagination.toLimitClause(totalNum));
            log.trace(sql_select.toString());
        }
        //创建预编译sql语句
        preparedStatement = connection.prepareStatement(sql_select.toString());
        //执行，获得查找到的额d
        resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            int teacher_id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String no = resultSet.getString("no");
            int department_id = resultSet.getInt("department_id");
            Department department = DepartmentDao.getInstance().find(connection,department_id);
            Teacher foundTeacher = new Teacher(teacher_id,name,no);
            foundTeacher.setDepartment(department);
            teachers.add(foundTeacher);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return teachers;
    }

    /**
     * 根据id在数据库中查找teacher对象
     * @param id 想要查找到teacher元组的id
     * @return 返回查找到的teacher对象
     * @throws SQLException
     */
    public Teacher find(Connection connection,Integer id) throws SQLException {
        Teacher foundTeacher = null;
        //预编译sql语句
        preparedStatement = connection.prepareStatement("SELECT * FROM teacher WHERE id=?");
        //为参数赋值，所查找元组的id
        preparedStatement.setInt(1,id);
        //执行sql语句，查找到的结果集
        resultSet = preparedStatement.executeQuery();
        if (resultSet.next()){
            //获得数据
            int teacher_id = resultSet.getInt("id");
            String name = resultSet.getString("name");
            String no = resultSet.getString("no");
            int department_id = resultSet.getInt("department_id");
            Department department = DepartmentDao.getInstance().find(connection,department_id);
            //为teacher字段赋值
            foundTeacher = new Teacher(teacher_id,name,no);
            foundTeacher.setDepartment(department);
        }
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement);
        //返回查找到的foundTeacher对象
        return foundTeacher;
    }

    /**
     * 用于事务。
     * 多次对Dao的调用保证是从一个connection对象，才能将他们设置在同一事物中
     * @param teacher 要修改到数据库的teacher对象
     * @param connection 来自Service的连接对象
     * @return 更新成功返回true，失败false
     * @throws SQLException
     */
    public boolean update(Teacher teacher,Connection connection) throws SQLException{
        //预编译sql语句
        preparedStatement = connection.prepareStatement("UPDATE teacher SET name=?,no=?,department_id=? WHERE id=?");
        //为参数赋值
        preparedStatement.setString(1,teacher.getName());
        preparedStatement.setString(2,teacher.getNo());
        preparedStatement.setInt(3,teacher.getDepartment().getId());
        preparedStatement.setInt(4,teacher.getId());
        //执行sql语句
        int rowAffected = preparedStatement.executeUpdate();
        //关闭链接
        JdbcHelper.close(null,preparedStatement);
        return rowAffected>0;
    }
    /**
     * 用于事务。调用本方法的Service，在调用本方法后，还会调用UserDao.add方法
     * 两次对Dao的调用保证是从一个connection对象，才能将他们设置在同一事物中
     * @param teacher 要增加到数据库的teacher对象
     * @param connection 来自Service的连接对象
     * @return 成功增加：新对象的id，失败：0
     * @throws SQLException
     */
    public boolean add(Teacher teacher, Connection connection) throws SQLException{
        //预编译sql语句
        this.preparedStatement = connection.prepareStatement("INSERT INTO teacher (id,name,no,department_id)VALUES (?,?,?,?)");
        //为sql语句参数设置值
        preparedStatement.setInt(1,teacher.getId());
        preparedStatement.setString(2,teacher.getName());
        preparedStatement.setString(3,teacher.getNo());
        preparedStatement.setInt(4,teacher.getDepartment().getId());
        //执行sql语句并获得受影响行数的值
        int rowAffected = preparedStatement.executeUpdate();
        //获得生成的主键的集合
        //不提交到数据库
        //新增元组的id
        JdbcHelper.close(null,preparedStatement);
        return rowAffected>0;
    }

    /**
     * 根据id删除教师
     * @param id
     * @param connection
     * @return
     * @throws SQLException
     */
    public boolean delete(Integer id,Connection connection) throws SQLException{
        preparedStatement = connection.prepareStatement("DELETE FROM teacher WHERE id=?");
        preparedStatement.setInt(1,id);
        //执行删除并获得相应的受影响行数的值
        int rowAffected = preparedStatement.executeUpdate();
        //返回删除是否成功
        return rowAffected>0;
    }
}
